﻿using MediatR;
using Microsoft.Data.SqlClient;
using S.Archives.Domain.S.ArchivesDto;
using S.Archives.Infrstructre;
using S.Archives.Read.Api.Application.Command;

namespace S.Archives.Read.Api.Application.CommandHandlers
{
    /// <summary>
    /// 挑选种公种母
    /// </summary>
    public class SelMaleEmaleCommandHandler : IRequestHandler<SelMaleEmaleCommand, List<SelMaleEmaleDto>>
    {
        private readonly DbHelper dbHelper;
        public SelMaleEmaleCommandHandler(DbHelper dbHelper)
        {
            this.dbHelper = dbHelper;
        }
        public async Task<List<SelMaleEmaleDto>> Handle(SelMaleEmaleCommand request, CancellationToken cancellationToken)
        {
            var sql = "SELECT b.SurrenderDate, a.ParentEarTag,a.MotherEarTag, a.SheepFileId,  a.EarTag, a.Variety, " +
     "CASE WHEN a.Variety = 1 THEN '杜泊' " +
     "     WHEN a.Variety = 2 THEN '澳洲白' " +
     "     WHEN a.Variety = 3 THEN '萨福克' " +
     "     WHEN a.Variety = 4 THEN '杜湖' " +
     "     WHEN a.Variety = 5 THEN '杜杜湖' " +
     "     WHEN a.Variety = 6 THEN '萨杜湖' " +
     "     WHEN a.Variety = 7 THEN '澳湖' " +
     "     WHEN a.Variety = 8 THEN '湖羊' " +
     "     WHEN a.Variety = 9 THEN '澳杜湖' " +
     "END AS VarietyLabel, " +
     "a.ReproductiveState, " +
     "CASE WHEN a.ReproductiveState = 1 THEN '后备空怀' " +
     "     WHEN a.ReproductiveState = 2 THEN '返情空怀级' " +
     "     WHEN a.ReproductiveState = 3 THEN '未孕空怀' " +
     "     WHEN a.ReproductiveState = 4 THEN '流产空怀' " +
     "     WHEN a.ReproductiveState = 5 THEN '空胎空怀' " +
     "     WHEN a.ReproductiveState = 6 THEN '断奶空怀' " +
     "     WHEN a.ReproductiveState = 7 THEN '妊娠' " +
     "     WHEN a.ReproductiveState = 8 THEN '哺乳' " +
     "END AS ReproductiveStateLabel, " +
     "DATEDIFF(MONTH, a.BirthDate, GETDATE()) + " +
     "CASE WHEN DATEPART(DAY, a.BirthDate) > DATEPART(DAY, GETDATE()) THEN -1 ELSE 0 END AS MonthAge, " +
     "DATEDIFF(DAY, a.BirthDate, GETDATE()) AS DayAge, " +
     "a.BirthDate, " +
     "a.BirthWeight, " +
     "a.GeneRank, " +
     "CASE WHEN a.GeneRank = 1 THEN '后备级' " +
     "     WHEN a.GeneRank = 2 THEN '核心级' " +
     "     WHEN a.GeneRank = 3 THEN '生产级' " +
     "     WHEN a.GeneRank = 4 THEN '未定级' " +
     "END AS GeneRankLabel, " +
     "c.BuildingName, " +
     "d.ColumnName " +
     "FROM SheepFileModel a " +
     "LEFT JOIN SheepRescission b ON a.EarTag = b.EarTag " +
     "LEFT JOIN ColumnManagementModel c ON b.ShiftToBuild = c.BuildingManagementId " +
     "LEFT JOIN FieldModel d ON b.ShiftToHuts = d.ColumnManagementId " +
     "WHERE FileType = @FileType";
            SqlParameter[] value =
            {
                new SqlParameter("@FileType",request.FileType)
            };
            var list=await dbHelper.GetListDataBySql<SelMaleEmaleDto>(sql, value);
            list=list.Where(x=>x.ReproductiveState<=6).ToList();
            var result = list.GroupBy(x => x.EarTag).Select(g => g.OrderByDescending(x => x.SurrenderDate).First()).ToList();
            return result;
        }
    }
}
